The development of the EDVAC computer system of 1948 is often cited as the beginning of the computer era. Since that time, computer systems have evolved into extremely sophisticated devices, and computer systems may be found in many different settings. Computer systems typically include a combination of hardware, such as semiconductors and circuit boards, and software, also known as computer programs.
Fundamentally, computer systems are used for the storage, manipulation, and analysis of data, which may be anything from complicated financial information to simple baking recipes. It is no surprise, then, that the overall value or worth of a computer system depends largely upon how well the computer system stores, manipulates, and analyzes data. One mechanism for managing data is called a database management system (DMS), which may also be called a database system or simply a database.
Many different types of databases are known, but the most common is usually called a relational database (RDB), which organizes data in tables that have rows, which represent individual entries or records in the database, and columns, which define what is stored in each entry or record. Each table has a unique name within the database and each column has a unique name within the particular table. The database also has an index, which is a data structure that informs the database management system of the location of a certain row in a table given an indexed column value, analogous to a book index informing the reader on which page a given word appears.
To be useful, the data stored in databases must be capable of being retrieved in an efficient manner. The most common way to retrieve data from a database is through statements called database queries. A query is an expression evaluated by the database management system. Queries may be issued programmatically via applications or via a user interface. As one might imagine, queries range from being very simple to very complex. When the database management system receives a query, the database management system interprets the query and determines what internal steps are necessary to satisfy the query. These internal steps may include an identification of the table or tables specified in the query, the row or rows selected in the query, and other information such as whether to use an existing index, whether to build a temporary index, whether to use a temporary file to execute a sort, and/or the order in which the tables are to be joined together to satisfy the query.
When taken together, these internal steps are referred to as an execution plan or an access plan. The access plan is typically created by a software component that is often called a query optimizer. The query optimizer may be part of the database management system or separate from but in communication with the database management system. When a query optimizer creates an access plan for a given query, the access plan is often saved by the database management system in the program object, e.g., the application program, that requested the query. The access plan may also be saved in an SQL (Structured Query Language) package or an access plan cache. Then, when the user or program object repeats the query, which is a common occurrence, the database management system can find and reutilize the associated saved access plan instead of undergoing the expensive and time-consuming process of recreating the access plan. Thus, reusing access plans increases the performance of queries when performed by the database management system.
For complex queries that are frequently executed, users may spend substantial time fine-tuning the access plan, in order to achieve the best possible performance. Once the access plan has been properly tuned, users naturally expect the query to keep performing well in the future. Unfortunately, an access plan that experienced acceptable or even optimal performance when it was initially created may experience significant performance degradation later because of changes that have occurred to the organization of the target database or changes that have occurred in the database management system, e.g., changes due to a new operating system release for the computer system. For example, the database that is the target of the query may have indexes added, changed, or removed (either intentionally or inadvertently), which can dramatically impact the performance of the previously tuned and saved access plan.
Users may have a very limited awareness of the source of or reason for these changes that have affected the performance of their queries. They may merely know that suddenly their queries are running much slower without any idea of the reason why. Further, the more complicated the query and its associated access plan, the more difficulty users experience when attempting to determine the source of the performance degradation.
Thus, without a better way to handle changes to databases and operating systems, users will continue to suffer difficulty in diagnosing and solving performance problems related to queries.